﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using System.Globalization;
namespace QLBDQG
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public SqlConnection mySqlConnection;
        SqlCommand mySqlCommand;
        SqlDataAdapter mySqlDataAdapter;
        DataSet myDataSet;
        List<CauThu> list = new List<CauThu>();
        DataTable myDataTable;
        DataRowView rowBeingEdited = null;
        string temple;
        bool tes;
        public MainWindow()
        {
            InitializeComponent();
         
        }
        public void makeTable()
        {   
            try
            {
                mySqlConnection = new SqlConnection();
                try
                {
                    // Open the connection
                    string path = System.IO.Directory.GetCurrentDirectory();
                    mySqlConnection.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + path +
                        @"\Data\QLBDQG.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
                    // _CNN.ConnectionString = @"Data Source=LANGQUANG-PC\SQLEXPRESS;Initial Catalog=QUANLYTIECCUOI;Integrated Security=True";
                    mySqlConnection.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Lỗi kết nối CSDL", "ERROR!");
                }

                mySqlCommand = new SqlCommand("SHOW", mySqlConnection);
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlDataAdapter = new SqlDataAdapter();
                mySqlDataAdapter.SelectCommand = mySqlCommand;
                myDataSet = new DataSet();

                mySqlDataAdapter.Fill(myDataSet, "CAUTHU");

                myDataTable = myDataSet.Tables["CAUTHU"];
                dataGrid1.ItemsSource = myDataTable.DefaultView;
                

            }
             catch (SqlException es)
             {
                 MessageBox.Show(es.Message.ToString());
             }       
        }
        private void make_Click(object sender, RoutedEventArgs e)
        {

            
        }

        private void btnNew_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                mySqlCommand = new SqlCommand("AddCauThu", mySqlConnection);
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlCommand.Parameters.Add(new SqlParameter("@TenCauThu",SqlDbType.VarChar,50));
                mySqlCommand.Parameters.Add(new SqlParameter("@NgaySinh", SqlDbType.Date));
                mySqlCommand.Parameters["@TenCauThu"].Value = "terung";
                mySqlCommand.Parameters["@NgaySinh"].Value = "5/4/1995";
                mySqlDataAdapter.SelectCommand = mySqlCommand;
                if(mySqlConnection.State==ConnectionState.Closed)
                {
                    mySqlConnection.Open();
                }
                mySqlDataAdapter.Fill(myDataSet, "CAUTHU");
            }
             catch (SqlException es)
             {
                 MessageBox.Show(es.Message.ToString());
             }
            makeTable();




        }

        private void dataGrid1_CellEditEnding(object sender, System.Windows.Controls.DataGridCellEditEndingEventArgs e)
        {
//             private DataRowView rowBeingEdited = null;
//  
//             private void dataGrid_CellEditEnding(object sender,
//                                               DataGridCellEditEndingEventArgs e)
//             {
//                 DataRowView rowView = e.Row.Item as DataRowView;
//                 rowBeingEdited = rowView;
//             }
//  
//             private void dataGrid_CurrentCellChanged(object sender, EventArgs e)
//             {
//                 if (rowBeingEdited != null)
//                 {
//                     rowBeingEdited.EndEdit();
//                 }
//             }
// 
//             DataRowView rowView = e.Row.Item as DataRowView;
//             rowBeingEdited = rowView;
        }

        private void dataGrid1_SelectionChanged(object sender, System.Windows.Controls.SelectionChangedEventArgs e)
        {

        }

        private void dataGrid1_CurrentCellChanged(object sender, EventArgs e)
        {
//             try
//             {
//                 SqlCommandBuilder commandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
//                 mySqlDataAdapter.Update(myDataSet, "CAUTHU");
//             }
//             catch (SqlException ees)
//             {
//                 MessageBox.Show(ees.Message.ToString());
//             }
        }

//                 try
//                 {
//                  

//                     string selectString =
//                         "UPDATE CAUTHU " +
//                         "SET " + Columedit + " = '" + valuedit + "'" +
//                         "WHERE MaCauThu = '" + rowedit + "'";
//                     mySqlCommand.CommandText = selectString;
//                     mySqlDataAdapter.SelectCommand = mySqlCommand;
//                     mySqlDataAdapter.Fill(myDataSet, "CAUTHU");
//                 }
//                 catch (SqlException es)
//                 {
// 
//                     MessageBox.Show(es.Message.ToString());
//                 }
//                 rowBeingEdited.EndEdit();
//                 tes = false;
//                
//             }
//             if (rowBeingEdited != null&&tes==true)
//             {
//                 try
//                 {
//                  
//                     string Columedit = myDataTable.Columns[dataGrid1.SelectedCells[0].Column.DisplayIndex].ColumnName;
//                     string rowedit = rowBeingEdited.Row[0].ToString();
//                     string valuedit = rowBeingEdited.Row[dataGrid1.SelectedCells[0].Column.DisplayIndex].ToString();
//                     string selectString =
//                         "UPDATE CAUTHU " +
//                         "SET " + Columedit + " = '" + valuedit + "'" +
//                         "WHERE MaCauThu = '" + rowedit + "'";
//                     mySqlCommand.CommandText = selectString;
//                     mySqlDataAdapter.SelectCommand = mySqlCommand;
//                     mySqlDataAdapter.Fill(myDataSet, "CAUTHU");
//                 }
//                 catch (SqlException es)
//                 {
// 
//                     MessageBox.Show(es.Message.ToString());
//                 }
//                 rowBeingEdited.EndEdit();
//                 tes = false;
//                
//             }
        

        private void dataGrid1_BeginningEdit(object sender, System.Windows.Controls.DataGridBeginningEditEventArgs e)
        {
           
        }

        private void dataGrid1_TextInput(object sender, TextCompositionEventArgs e)
        {
            
        }

        private void dataGrid1_SelectedCellsChanged(object sender, System.Windows.Controls.SelectedCellsChangedEventArgs e)
        {
            rowBeingEdited = dataGrid1.SelectedCells[0].Item as DataRowView;
            textBox3.Text = rowBeingEdited[1].ToString();
        }

        private void dataGrid1_RowEditEnding(object sender, System.Windows.Controls.DataGridRowEditEndingEventArgs e)
        {
            
        }

        private void dataGrid1_SourceUpdated(object sender, DataTransferEventArgs e)
        {
            
        }

        private void dataGrid1_KeyUp(object sender, KeyEventArgs e)
        {
            
        }

        private void dataGrid1_MouseDoubleClick(object sender, MouseButtonEventArgs e)
        {
        }

        private void textBox2_TextChanged(object sender, TextChangedEventArgs e)
        {
            
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            makeTable();
        }
    }





    public class CauThu
    {
        public Regex nameEx = new Regex(@"^[A-Za-z ]+$");
        private int macauthu;
        public int MaCauThu
        {
            get { return macauthu; }
            set { macauthu = value; }
        }
        string tencauthu;
        public string TenCauThu
        {
            

            get { return tencauthu; }
            set
            {
                if (value == null)
                    throw new Exception("Name cannot be null");

                if (!nameEx.Match(value).Success)
                {
                    throw new Exception("Name may only " +
                              "contain characters or spaces");
                }
                
                tencauthu = value;
            }
        }
        public DateTime NgaySinh { get; set; }


    }
}


